﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.Data.OleDb;
using System.IO;

namespace DataProcess {
	public partial class frmDataCompare : Form {
		OleDbConnection conn = new OleDbConnection();
		public frmDataCompare() {
			InitializeComponent();
		}

		private void button1_Click(object sender, EventArgs e) {
			OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
			builder.Provider = "Microsoft.Jet.OLEDB.4.0";
			string cString = textBox1.Text;
			if (String.IsNullOrEmpty(cString)) {
				MessageBox.Show("请选择数据库文件！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			if (cString.IndexOfAny(new char[] { '/', '\\' }) == -1) {
				cString = Path.Combine(Application.StartupPath, cString);
			}
			if (!File.Exists(cString)) {
				MessageBox.Show("数据库文件不存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			builder.DataSource = cString;
			conn.ConnectionString = builder.ConnectionString;
			try {
				conn.Open();
			}
			catch (Exception) {
				MessageBox.Show("不能打开数据库！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			if (conn.State != ConnectionState.Open) {
				MessageBox.Show("不能打开数据库！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			bw.RunWorkerAsync(conn);
			button5.Enabled = false;
			button1.Enabled = false;
			textBox1.Enabled = false;
			button2.Enabled = true;
		}

		private void 合并药物组的成分(OleDbConnection conn, BackgroundWorker bw, DoWorkEventArgs e) {
			var 药物组 = new compareDataSetTableAdapters.药物组与成分对应关系表TableAdapter();
			var drug_ing = new compareDataSetTableAdapters.药物组中的成分合并TableAdapter();
			药物组.Connection = drug_ing.Connection = conn;
			var gendata = 药物组.GetData().GroupBy(item => item.通用药物组id);
			var data = new compareDataSet.药物组中的成分合并DataTable();

			var percentage = 0;
			int total = gendata.Count();
			foreach (var item in gendata) {
				data.Add药物组中的成分合并Row(item.Key, string.Join(",", item.Select(gen => gen.成分组ID)));
				percentage++;
				bw.ReportProgress((int)((double)percentage * 100 / (double)total));
				if (bw.CancellationPending) { e.Cancel = true; return; }
			}
			bw.ReportProgress(100, "药物组中的成分合并：写入数据库");
			drug_ing.Update(data);
		}

		private void 合并说明书的成分(OleDbConnection conn, BackgroundWorker bw, DoWorkEventArgs e) {
			var piadp = new compareDataSetTableAdapters.说明书与成分对应关系表_唯一化TableAdapter();
			var pihb = new compareDataSetTableAdapters.说明书中的成分合并TableAdapter();
			piadp.Connection = pihb.Connection = conn;
			var pi_ings_group = piadp.GetDataByIngs().GroupBy(item => item.通用药物组id);
			var data = new compareDataSet.说明书中的成分合并DataTable();
			var percentage = 0;
			int total = pi_ings_group.Count();
			foreach (var item in pi_ings_group) {
				data.Add说明书中的成分合并Row(item.Key, string.Join(",", item.Select(ing => ing.成分组ID)));
				percentage++;
				bw.ReportProgress((int)((double)percentage * 100 / (double)total));
				if (bw.CancellationPending) { e.Cancel = true; return; }
			}
			bw.ReportProgress(100, "说明书中的成分合并：写入数据库");
			pihb.Update(data);
		}
		private void 多出和缺少的成分(OleDbConnection conn, BackgroundWorker bw, DoWorkEventArgs e) {
			var pi = new compareDataSetTableAdapters.说明书与成分对应关系表_唯一化TableAdapter();
			var gens = new compareDataSetTableAdapters.药物组与成分对应关系表TableAdapter();
			var difadp = new compareDataSetTableAdapters.和药物组成分不同且文章的成分不同详细TableAdapter();
			pi.Connection = gens.Connection = difadp.Connection = conn;
			var difData = difadp.GetData();
			var percentage = 0;
			int total = difData.Count();
			foreach (var item in difData) {
				var allIngs = pi.GetDataByGenDoc(item.通用药物组id, item.docid);
				item.成分组ID = string.Join(",", allIngs.Select(pirow => pirow.成分组ID));
				item.成分组名称 = string.Join(",", allIngs.Select(pirow => pirow.成分中文通用名称));
				var 多出的成分 = allIngs.Where(p => p.多出的成分 == 1);
				item.多出的成分组ID = string.Join(",", 多出的成分.Select(p => p.成分组ID));
				item.多出的成分组名称 = string.Join(",", 多出的成分.Select(p => p.成分中文通用名称));
				var 缺少的成分 = gens.GetDataByNotInPi(item.通用药物组id, item.docid);
				item.缺少的成分组ID = string.Join(",", 缺少的成分.Select(p => p.成分组ID));
				item.缺少的成分组名称 = string.Join(",", 缺少的成分.Select(p => p.成分中文通用名称));
				percentage++;
				bw.ReportProgress((int)((double)percentage * 100 / (double)total));
				if (bw.CancellationPending) { e.Cancel = true; return; }
			}
			bw.ReportProgress(100, "多出和缺少的成分：写入数据库");
			difadp.Update(difData);
		}
		private void button5_Click(object sender, EventArgs e) {
			if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
				textBox1.Text = openFileDialog1.FileName;
			}
		}

		private void bw_DoWork(object sender, DoWorkEventArgs e) {
			var bw = sender as BackgroundWorker;
			var conn = e.Argument as OleDbConnection;
			var cmd = conn.CreateCommand();
			bw.ReportProgress(0, "清理数据");
			#region 清理数据和建表
			var tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
			if (tables.Select("[TABLE_NAME]='说明书与成分对应关系表_唯一化'").Length > 0) {
				cmd.CommandText = "drop table 说明书与成分对应关系表_唯一化";
				cmd.ExecuteNonQuery();
			}
			cmd.CommandText = "create table 说明书与成分对应关系表_唯一化(ID autoincrement(1,1) primary key,通用药物组id INT,docid INT,成分组ID INT,成分中文通用名称 TEXT(255),多出的成分 INT DEFAULT 0)";
			cmd.ExecuteNonQuery();
			cmd.CommandText = "CREATE INDEX 通用药物组id_成分组 on 说明书与成分对应关系表_唯一化(通用药物组id ASC,成分组ID)";
			cmd.ExecuteNonQuery();
			cmd.CommandText = "CREATE INDEX 通用药物组id_docid on 说明书与成分对应关系表_唯一化(通用药物组id ASC,docid ASC)";
			cmd.ExecuteNonQuery();
			if (tables.Select("[TABLE_NAME]='药物组中的成分合并'").Length > 0) {
				cmd.CommandText = "drop table 药物组中的成分合并";
				cmd.ExecuteNonQuery();
			}
			cmd.CommandText = "create table 药物组中的成分合并(genid int primary key,ingids memo)";
			cmd.ExecuteNonQuery();
			if (tables.Select("[TABLE_NAME]='说明书中的成分合并'").Length > 0) {
				cmd.CommandText = "drop table 说明书中的成分合并";
				cmd.ExecuteNonQuery();
			}
			cmd.CommandText = "create table 说明书中的成分合并(genid int primary key,ingids memo)";
			cmd.ExecuteNonQuery();
			if (tables.Select("[TABLE_NAME]='和药物组成分不同且文章的成分不同详细'").Length > 0) {
				cmd.CommandText = "drop table 和药物组成分不同且文章的成分不同详细";
				cmd.ExecuteNonQuery();
			}
			cmd.CommandText = "create table 和药物组成分不同且文章的成分不同详细(ID autoincrement(1,1) primary key,通用药物组id INT,docid INT,成分组ID MEMO,成分组名称 MEMO,文章成分相同 INT default 0,多出的成分组ID MEMO,多出的成分组名称 MEMO,缺少的成分组ID MEMO,缺少的成分组名称 MEMO)";
			cmd.ExecuteNonQuery();
			bw.ReportProgress(100);
			#endregion
			if (bw.CancellationPending) { e.Cancel = true; return; }
			bw.ReportProgress(0, "说明书对应成分唯一化");
			cmd.CommandText = "INSERT INTO 说明书与成分对应关系表_唯一化 ( 通用药物组id, docid, 成分组ID, 成分中文通用名称 ) SELECT DISTINCT 说明书与成分对应关系表.通用药物组id, 说明书与成分对应关系表.docid, 说明书与成分对应关系表.成分组ID, 说明书与成分对应关系表.成分中文通用名称 FROM 说明书与成分对应关系表 ORDER BY 说明书与成分对应关系表.通用药物组id, 说明书与成分对应关系表.docid, 说明书与成分对应关系表.成分组ID";
			cmd.ExecuteNonQuery();
			if (bw.CancellationPending) { e.Cancel = true; return; }
			bw.ReportProgress(0, "标记多出的成分");
			cmd.CommandText = "UPDATE 说明书与成分对应关系表_唯一化 SET 说明书与成分对应关系表_唯一化.多出的成分 = 1 WHERE NOT Exists (select 成分组ID from [药物组与成分对应关系表] where [通用药物组id]=[说明书与成分对应关系表_唯一化].[通用药物组id] and [成分组ID]=[说明书与成分对应关系表_唯一化].[成分组ID])";
			cmd.ExecuteNonQuery();
			if (bw.CancellationPending) { e.Cancel = true; return; }
			bw.ReportProgress(0, "说明书中的成分合并");
			合并说明书的成分(conn, bw, e);
			if (bw.CancellationPending) { e.Cancel = true; return; }
			bw.ReportProgress(0, "药物组中的成分合并");
			合并药物组的成分(conn, bw, e);
			if (bw.CancellationPending) { e.Cancel = true; return; }
			bw.ReportProgress(0, "对比不同的数据");
			cmd.CommandText = "INSERT INTO [和药物组成分不同且文章的成分不同详细] ( 通用药物组id, docid ) SELECT DISTINCT [说明书与成分对应关系表_唯一化].通用药物组id, [说明书与成分对应关系表_唯一化].docid FROM [说明书与成分对应关系表_唯一化] INNER JOIN (说明书中的成分合并 INNER JOIN 药物组中的成分合并 ON 说明书中的成分合并.genid = 药物组中的成分合并.genid) ON [说明书与成分对应关系表_唯一化].通用药物组id = 说明书中的成分合并.genid WHERE (((说明书中的成分合并.ingids)<>[药物组中的成分合并].[ingids])) ORDER BY [说明书与成分对应关系表_唯一化].通用药物组id, [说明书与成分对应关系表_唯一化].docid";
			cmd.ExecuteNonQuery();
			if (bw.CancellationPending) { e.Cancel = true; return; }
			bw.ReportProgress(0, "多出和缺少的成分");
			多出和缺少的成分(conn, bw, e);
			if (bw.CancellationPending) { e.Cancel = true; return; }
			bw.ReportProgress(0, "标记相同成分的组");
			cmd.CommandText = "UPDATE 和药物组成分不同且文章的成分不同详细 SET 文章成分相同 = 1 WHERE 通用药物组id In (SELECT ings_Alias.通用药物组id FROM (SELECT 和药物组成分不同且文章的成分不同详细.通用药物组id, 和药物组成分不同且文章的成分不同详细.成分组ID FROM 和药物组成分不同且文章的成分不同详细 GROUP BY 和药物组成分不同且文章的成分不同详细.通用药物组id, 和药物组成分不同且文章的成分不同详细.成分组ID)  AS ings_Alias GROUP BY ings_Alias.通用药物组id HAVING (((Count(*))=1)))";
			cmd.ExecuteNonQuery();
			bw.ReportProgress(0, "就绪");
		}

		private void bw_ProgressChanged(object sender, ProgressChangedEventArgs e) {
			progressBar1.Value = e.ProgressPercentage;
			if (e.UserState != null) {
				label2.Text = e.UserState.ToString();
			}
		}

		private void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) {
			MessageBox.Show("完成");
			if (conn.State == ConnectionState.Open) {
				conn.Close();
			}
			button5.Enabled = true;
			button1.Enabled = true;
			textBox1.Enabled = true;
			button2.Enabled = false;
		}

		private void button2_Click(object sender, EventArgs e) {
			if (bw.IsBusy) {
				bw.CancelAsync();
			}
		}
	}

	class pi_drug {
		public int genid { get; set; }
		public List<string> ings { get; set; }
	}
}
